﻿using NPOI.HSSF.UserModel;
using OMG.Fill.Potato.Model.Ent;
using OMG.Fill.Potato.Services;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Web;
using System.Web.Http;
namespace OMG.Fill.Potato.Model.UI.Controllers
{
    public class PotSeasonProductController : ApiController
    {
        PotSeasonProductServices potSeasonProductServices = new PotSeasonProductServices();
        
        //[ApiActionAuthorize("获取各生产季马铃薯生产情况统计表分页列表对象操作")]
        //[ApiActionException]
        public Pager<List<PotSeasonProductEntity>> PostPageList(PotSeasonProductQuery query)
        {
            return potSeasonProductServices.GetPageList(query);
        }
        //[ApiActionAuthorize("新增各生产季马铃薯生产情况统计表指定对象操作")]
        //[ApiActionException]
        public ResResult<int> Post(List<PotSeasonProductEntity> list) {
            return potSeasonProductServices.AddInfo(list);
        }
        //[ApiActionAuthorize("修改各生产季马铃薯生产情况统计表指定对象操作")]
        //[ApiActionException]
        public ResResult<int> Put(PotSeasonProductEntity model)
        {
            return potSeasonProductServices.UpdateInfo(model);
        }
        //[ApiActionAuthorize("删除各生产季马铃薯生产情况统计表指定对象操作")]
        //[ApiActionException]
        public ResResult<int> Delete(string id)
        {
            return potSeasonProductServices.DeleteInfo(id);
        }
        //[ApiActionAuthorize("获取各生产季马铃薯生产情况统计表指定对象操作")]
        //[ApiActionException]
        public ResResult<List<PotSeasonProductEntity>> Get(string ReportId, string SeasonValue)
        {
            return potSeasonProductServices.GetModel(ReportId, SeasonValue);
        }
        public ResResult<List<PotSeasonProductEntity>> Get(string AreaCode,string FillInYear, string TimesCycle)
        {
            return potSeasonProductServices.GetValidData(AreaCode, FillInYear, TimesCycle);
        }
        public ResResult<List<List<AllYearStatisModel>>> GetAllYearStatisList(string AreaCode, string TimesCycle, string SeasonDict)
        {
            return potSeasonProductServices.GetAllYearStatisList(AreaCode,TimesCycle, SeasonDict);
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="ReportId"></param>
        /// <param name="FillInYear"></param>
        /// <param name="TimesCycle"></param>
        /// <param name="AreaCode"></param>
        public void GetExcelData(string AreaCode,string FillInYear,string TimesCycle)
        {
            AreaServices areaServices = new AreaServices();
            string AreaName = areaServices.GetByCodeModel(AreaCode).model.Name;
            //打开完成excel文件并下载
            HttpContext curContext = HttpContext.Current;
            // 设置编码和附件格式
            curContext.Response.AddHeader("Access-Control-Allow-Origin", "*");
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            string TimesCycleName = "";
            switch (TimesCycle)
            {
                case "1":
                    TimesCycleName = "大春";
                    break;
                case "2":
                    TimesCycleName = "秋作";
                    break;
                case "3":
                    TimesCycleName = "冬作";
                    break;
            }
            string strFileName = "（二）" + FillInYear + "年-" + TimesCycleName+ "-" + AreaName + "-马铃薯生产情况统计表.xls";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
            curContext.Response.BinaryWrite(Method(AreaCode, FillInYear, TimesCycle).GetBuffer());
            curContext.Response.End();
        }
        /// <summary>
        /// 获取导出数据
        /// </summary>
        /// <param name="ReportId"></param>
        /// <param name="TimesCycle"></param>
        /// <returns></returns>
        public MemoryStream Method(string AreaCode, string FillInYear, string TimesCycle)
        {
            FileStream file = null;
            MemoryStream ms = new MemoryStream();
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\PotDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("PotSeasonProduct") as HSSFSheet;//读取模板所在sheet位置
            List<PotSeasonProductEntity> pagelist = potSeasonProductServices.GetValidData(AreaCode, FillInYear, TimesCycle).model;//数据获取
            try
            {
                if (pagelist.Count != 0)
                {
                    HSSFRow row = (HSSFRow)sheet.GetRow(4);//读取模板第5行                   
                    int startRow = 4;
                    for (int j = 0; j < pagelist.Count; j++)
                    {
                        HSSFRow datarow = (HSSFRow)sheet.CreateRow(j + startRow);
                        datarow.CreateCell(0).SetCellValue(pagelist[j].SeasonDict == null ? "" : pagelist[j].SeasonDict);
                        datarow.CreateCell(1).SetCellValue(pagelist[j].PHTime == null ? "" : pagelist[j].PHTime);
                        //datarow.CreateCell(2).SetCellValue(pagelist[j].MainVariety == null ? "" :  pagelist[j].MainVariety);
                        datarow.CreateCell(2).SetCellValue(pagelist[j].TotalArea == null ? "" : string.Format("{0:N4}", pagelist[j].TotalArea));
                        datarow.CreateCell(3).SetCellValue(pagelist[j].TotalYield == null ? "" : string.Format("{0:N4}", pagelist[j].TotalYield));
                        datarow.CreateCell(4).SetCellValue(pagelist[j].AvgMuYield == null ? "" : string.Format("{0:N2}", pagelist[j].AvgMuYield));
                        datarow.CreateCell(5).SetCellValue(pagelist[j].MuOutput == null ? "" : string.Format("{0:N1}", pagelist[j].MuOutput));
                        datarow.CreateCell(6).SetCellValue(pagelist[j].TotalOutput == null ? "" : string.Format("{0:N4}", pagelist[j].TotalOutput));
                        datarow.CreateCell(7).SetCellValue(pagelist[j].TechModel == null ? "" : pagelist[j].TechModel);
                        datarow.CreateCell(8).SetCellValue(pagelist[j].tdSeedArea == null ? "" : string.Format("{0:N4}", pagelist[j].tdSeedArea));
                        datarow.CreateCell(9).SetCellValue(pagelist[j].SeedCoverage == null ? "" : string.Format("{0:N2}", pagelist[j].SeedCoverage));
                        datarow.CreateCell(10).SetCellValue(pagelist[j].MulchArea == null ? "" : string.Format("{0:N4}", pagelist[j].MulchArea));
                        datarow.CreateCell(11).SetCellValue(pagelist[j].DripArea == null ? "" : string.Format("{0:N4}", pagelist[j].DripArea));
                        datarow.CreateCell(12).SetCellValue(pagelist[j].FertilizerArea == null ? "" : string.Format("{0:N4}", pagelist[j].FertilizerArea));
                        datarow.CreateCell(13).SetCellValue(pagelist[j].NoCultivationArea == null ? "" : string.Format("{0:N4}", pagelist[j].NoCultivationArea));
                        datarow.CreateCell(14).SetCellValue(pagelist[j].JZArea == null ? "" : string.Format("{0:N4}", pagelist[j].JZArea));
                        datarow.CreateCell(15).SetCellValue(pagelist[j].JZYield == null ? "" : string.Format("{0:N4}", pagelist[j].JZYield));
                        datarow.CreateCell(16).SetCellValue(pagelist[j].JZAvgMuYield == null ? "" : string.Format("{0:N2}", pagelist[j].JZAvgMuYield));
                        datarow.CreateCell(17).SetCellValue(pagelist[j].JZMuOutput == null ? "" : string.Format("{0:N1}", pagelist[j].JZMuOutput));
                        datarow.CreateCell(18).SetCellValue(pagelist[j].JZDensity == null ? "" : string.Format("{0:N0}", pagelist[j].JZDensity));
                        datarow.CreateCell(19).SetCellValue(pagelist[j].JZTechModel == null ? "" : pagelist[j].JZTechModel);
                        datarow.CreateCell(20).SetCellValue(pagelist[j].JTArea == null ? "" : string.Format("{0:N4}", pagelist[j].JTArea));
                        datarow.CreateCell(21).SetCellValue(pagelist[j].JTYield == null ? "" : string.Format("{0:N4}", pagelist[j].JTYield));
                        datarow.CreateCell(22).SetCellValue(pagelist[j].JTAvgMuYield == null ? "" : string.Format("{0:N2}", pagelist[j].JTAvgMuYield));
                        datarow.CreateCell(23).SetCellValue(pagelist[j].JTMixMuYield == null ? "" : string.Format("{0:N2}", pagelist[j].JTMixMuYield));
                        datarow.CreateCell(24).SetCellValue(pagelist[j].JTMixMuOutput == null ? "" : string.Format("{0:N1}", pagelist[j].JTMixMuOutput));
                        datarow.CreateCell(25).SetCellValue(pagelist[j].JTTechModel == null ? "" : pagelist[j].JTTechModel);
                        datarow.CreateCell(26).SetCellValue(pagelist[j].MechPlantArea == null ? "" : string.Format("{0:N4}", pagelist[j].MechPlantArea));
                        datarow.CreateCell(27).SetCellValue(pagelist[j].MechCultivationArea == null ? "" : string.Format("{0:N4}", pagelist[j].MechCultivationArea));
                        datarow.CreateCell(28).SetCellValue(pagelist[j].MechFertilizerArea == null ? "" : string.Format("{0:N4}", pagelist[j].MechFertilizerArea));
                        datarow.CreateCell(29).SetCellValue(pagelist[j].MechPesticideArea == null ? "" : string.Format("{0:N4}", pagelist[j].MechPesticideArea));
                        datarow.CreateCell(30).SetCellValue(pagelist[j].MechHarvestArea == null ? "" : string.Format("{0:N4}", pagelist[j].MechHarvestArea));
                        datarow.CreateCell(31).SetCellValue(pagelist[j].SeedCost == null ? "" : string.Format("{0:N1}", pagelist[j].SeedCost));
                        datarow.CreateCell(32).SetCellValue(pagelist[j].FertilizerCost == null ? "" : string.Format("{0:N1}", pagelist[j].FertilizerCost));
                        datarow.CreateCell(33).SetCellValue(pagelist[j].PesticideCost == null ? "" : string.Format("{0:N1}", pagelist[j].PesticideCost));
                        datarow.CreateCell(34).SetCellValue(pagelist[j].MulchCost == null ? "" : string.Format("{0:N1}", pagelist[j].MulchCost));
                        datarow.CreateCell(35).SetCellValue(pagelist[j].LaborCost == null ? "" : string.Format("{0:N1}", pagelist[j].LaborCost));
                        datarow.CreateCell(36).SetCellValue(pagelist[j].OtherCost == null ? "" : string.Format("{0:N1}", pagelist[j].OtherCost));
                        datarow.CreateCell(37).SetCellValue(pagelist[j].TotalCost == null ? "" : string.Format("{0:N1}", pagelist[j].TotalCost));
                        datarow.CreateCell(38).SetCellValue(pagelist[j].OAvgMuYield == null ? "" : string.Format("{0:N1}", pagelist[j].OAvgMuYield));
                        datarow.CreateCell(39).SetCellValue(pagelist[j].OAvgPrice == null ? "" : string.Format("{0:N1}", pagelist[j].OAvgPrice));
                        datarow.CreateCell(40).SetCellValue(pagelist[j].OTotalOutput == null ? "" : string.Format("{0:N1}", pagelist[j].OTotalOutput));
                        datarow.CreateCell(41).SetCellValue(pagelist[j].Income == null ? "" : string.Format("{0:N1}", pagelist[j].Income));
                        for (int i = 0; i <= 41; i++)
                        {
                            datarow.GetCell(i).CellStyle = row.GetCell(i).CellStyle;
                        }
                    }
                    sheet.CopyTo(workbook, "Sheet1", true, true);
                    workbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;

                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.ToString().Trim());
            }
            return ms;
        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="ReportId"></param>
        /// <returns></returns>
        public ResResult<int> PostImportData(string ReportId)
        {
            ResResult<int> obj = new ResResult<int>();
            ResResult<int> SingleObj = new ResResult<int>();
            HttpContext curContext = HttpContext.Current;
            HttpFileCollection files = curContext.Request.Files;

            if (curContext.Request.Files.Count != 0)
            {
                HttpPostedFile upfile = files[0];
                if (upfile != null && upfile.ContentLength > 0)
                {
                    string temppath = curContext.Server.MapPath("~/App_Data/temppath");
                    if (!Directory.Exists(temppath))//判断文件夹是否存在 
                    {
                        Directory.CreateDirectory(temppath);//不存在则创建文件夹 
                    }
                    string filetype = Path.GetExtension(upfile.FileName);
                    string filenewName = Guid.NewGuid().ToString().Replace("-", "") + filetype;
                    string strpath = Path.Combine(temppath, filenewName);
                    upfile.SaveAs(strpath);
                    List<PotSeasonProductEntity> list = potSeasonProductServices.ExcelToModel(strpath, filetype, ReportId);
                    if (list != null && list.Count > 0&& potSeasonProductServices.AddInfo(list).status==DefaultKey.SuccessResult)
                    {
                        obj.status = DefaultKey.SuccessResult; ;
                        obj.message = "数据导入成功！！";
                    }
                    else
                    {
                        obj.status = DefaultKey.ErrorResult; ;
                        obj.message = "数据导入失败！！";
                    }

                    Directory.Delete(temppath, true);
                }
            }

            return obj;
        }
        /// <summary>
        /// 获取统计列表数据
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <param name="Timescycle"></param>
        /// <returns></returns>
        public ResResult<List<SeasonProductStatisModel>> GetStatisListData(string AreaCode, string FillInYear, string Timescycle,string SeasonDict)
        {
            return potSeasonProductServices.getStatisListData(AreaCode, FillInYear, Timescycle, SeasonDict);
        }

        /// <summary>
        /// 统计导出
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <param name="Timescycle"></param>
        public void GetStatisExcel(string AreaCode, string FillInYear, string TimesCycle,string SeasonDict)
        {
            AreaServices areaServices = new AreaServices();
            string AreaName = areaServices.GetByCodeModel(AreaCode).model.Name;
            //打开完成excel文件并下载
            HttpContext curContext = HttpContext.Current;
            // 设置编码和附件格式
            curContext.Response.AddHeader("Access-Control-Allow-Origin", "*");
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            string TimesCycleName = "";
            switch (TimesCycle)
            {
                case "1":
                    TimesCycleName = "大春";
                    break;
                case "2":
                    TimesCycleName = "秋作";
                    break;
                case "3":
                    TimesCycleName = "冬作";
                    break;
            }
            string strFileName = "（二）" + FillInYear + "年-" + TimesCycleName + ((!String.IsNullOrEmpty(SeasonDict)) ? "之"+SeasonDict:"")+"-" + AreaName + "-马铃薯生产情况统计表.xls";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
            //curContext.Response.AppendHeader("Content-Disposition",
            //   "attachment;filename=" + strFileName);
            curContext.Response.BinaryWrite(loadStatis(AreaCode, FillInYear, TimesCycle, SeasonDict).GetBuffer());
            curContext.Response.End();
        }

        public MemoryStream loadStatis(string AreaCode, string FillInYear,string TimesCycle,string SeasonDict)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\PotStatisLoad.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet tempsheet = templateBook.GetSheet("PotSeasonProduct") as HSSFSheet;//读取模板所在sheet位置
            List<SeasonProductStatisModel> pagelist = (List<SeasonProductStatisModel>)potSeasonProductServices.getStatisListData(AreaCode, FillInYear, TimesCycle,SeasonDict).model;//数据获取

            HSSFRow row = (HSSFRow)tempsheet.GetRow(4);//读取模板第5行
            var style = row.GetCell(1).CellStyle;//读取模板第5行第2单元格样式并保存
            row.GetCell(1).CellStyle = style;//对第3行第2单元格设置原来样式

            HSSFSheet sheet = CopyToSheet(tempsheet, pagelist);
            AreaServices areaServices = new AreaServices();
            string areaName = areaServices.GetByCodeModel(AreaCode).model.Name;
            sheet.CopyTo(workbook, areaName, true, true);

            //if (AreaCode.Equals("530000000000"))
            //{
            //    var citys = areaServices.GetPrefectureList();
            //    foreach (string areacode in citys)
            //    {
            //        List<PromotionStatisModel> citylist = potPromotionServices.getStatisList(areacode, FillInYear);
            //        FileStream fm = new FileStream(templateName, FileMode.Open, FileAccess.Read);
            //        HSSFWorkbook Book = new HSSFWorkbook(fm);
            //        HSSFSheet temp = Book.GetSheet("PotPromotion") as HSSFSheet;
            //        HSSFRow row1 = (HSSFRow)temp.GetRow(4);
            //        HSSFSheet sheet1 = CopyToSheet(temp, citylist);
            //        string areaName1 = areaServices.GetByCodeModel(areacode).model.Name;
            //        sheet1.CopyTo(workbook, areaName1, true, true);
            //    }
            //}

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        public HSSFSheet CopyToSheet(HSSFSheet sheet, List<SeasonProductStatisModel> list)
        {
            HSSFRow row = (HSSFRow)sheet.GetRow(4);
            if (list.Count > 0)
            {
                int startRow = 4;
                for (int j = 0; j < list.Count; j++)
                {
                    HSSFRow datarow = (HSSFRow)sheet.CreateRow(j + startRow);
                    datarow.CreateCell(0).SetCellValue(list[j].AreaName == null ? "" : list[j].AreaName);
                    datarow.CreateCell(1).SetCellValue(list[j].TotalArea == null ? "" : string.Format("{0:N4}", list[j].TotalArea));
                    datarow.CreateCell(2).SetCellValue(list[j].TotalYield == null ? "" : string.Format("{0:N4}", list[j].TotalYield));
                    datarow.CreateCell(3).SetCellValue(list[j].TotalOutput == null ? "" : string.Format("{0:N4}", list[j].TotalOutput));
                    datarow.CreateCell(4).SetCellValue(list[j].AvgMuYield == null ? "" : string.Format("{0:N2}", list[j].AvgMuYield));
                    datarow.CreateCell(5).SetCellValue(list[j].MuOutput == null ? "" : string.Format("{0:N1}", list[j].MuOutput));
                    datarow.CreateCell(6).SetCellValue(list[j].tdSeedArea == null ? "" : string.Format("{0:N4}", list[j].tdSeedArea));
                    datarow.CreateCell(7).SetCellValue(list[j].SeedCoverage == null ? "" : string.Format("{0:N2}", list[j].SeedCoverage));
                    datarow.CreateCell(8).SetCellValue(list[j].MulchArea == null ? "" : string.Format("{0:N4}", list[j].MulchArea));
                    datarow.CreateCell(9).SetCellValue(list[j].DripArea == null ? "" : string.Format("{0:N4}", list[j].DripArea));
                    datarow.CreateCell(10).SetCellValue(list[j].FertilizerArea == null ? "" : string.Format("{0:N4}", list[j].FertilizerArea));
                    datarow.CreateCell(11).SetCellValue(list[j].NoCultivationArea == null ? "" : string.Format("{0:N4}", list[j].NoCultivationArea));
                    datarow.CreateCell(12).SetCellValue(list[j].JZArea == null ? "" : string.Format("{0:N4}", list[j].JZArea));
                    datarow.CreateCell(13).SetCellValue(list[j].JZYield == null ? "" : string.Format("{0:N4}", list[j].JZYield));
                    datarow.CreateCell(14).SetCellValue(list[j].JZAvgMuYield == null ? "" : string.Format("{0:N2}", list[j].JZAvgMuYield));
                    datarow.CreateCell(15).SetCellValue(list[j].JZOutput == null ? "" : string.Format("{0:N4}", list[j].JZOutput));
                    datarow.CreateCell(16).SetCellValue(list[j].JZMuOutput == null ? "" : string.Format("{0:N1}", list[j].JZMuOutput));
                    datarow.CreateCell(17).SetCellValue(list[j].JZDensity == null ? "" : string.Format("{0:N0}", list[j].JZDensity));
                    
                    datarow.CreateCell(18).SetCellValue(list[j].JTArea == null ? "" : string.Format("{0:N4}", list[j].JTArea));
                    datarow.CreateCell(19).SetCellValue(list[j].JTYield == null ? "" : string.Format("{0:N4}", list[j].JTYield));
                    datarow.CreateCell(20).SetCellValue(list[j].JTAvgMuYield == null ? "" : string.Format("{0:N2}", list[j].JTAvgMuYield));
                    datarow.CreateCell(21).SetCellValue(list[j].JTMixYield == null ? "" : string.Format("{0:N4}", list[j].JTMixYield));
                    datarow.CreateCell(22).SetCellValue(list[j].JTMixOutput == null ? "" : string.Format("{0:N4}", list[j].JTMixOutput));
                    datarow.CreateCell(23).SetCellValue(list[j].JTMixMuYield == null ? "" : string.Format("{0:N2}", list[j].JTMixMuYield));
                    datarow.CreateCell(24).SetCellValue(list[j].JTMixMuOutput == null ? "" : string.Format("{0:N1}", list[j].JTMixMuOutput));
                    
                    datarow.CreateCell(25).SetCellValue(list[j].MechPlantArea == null ? "" : string.Format("{0:N4}", list[j].MechPlantArea));
                    datarow.CreateCell(26).SetCellValue(list[j].MechCultivationArea == null ? "" : string.Format("{0:N4}", list[j].MechCultivationArea));
                    datarow.CreateCell(27).SetCellValue(list[j].MechFertilizerArea == null ? "" : string.Format("{0:N4}", list[j].MechFertilizerArea));
                    datarow.CreateCell(28).SetCellValue(list[j].MechPesticideArea == null ? "" : string.Format("{0:N4}", list[j].MechPesticideArea));
                    datarow.CreateCell(29).SetCellValue(list[j].MechHarvestArea == null ? "" : string.Format("{0:N4}", list[j].MechHarvestArea));
                    datarow.CreateCell(30).SetCellValue(list[j].SeedCost == null ? "" : string.Format("{0:N1}", list[j].SeedCost));
                    datarow.CreateCell(31).SetCellValue(list[j].FertilizerCost == null ? "" : string.Format("{0:N1}", list[j].FertilizerCost));
                    datarow.CreateCell(32).SetCellValue(list[j].PesticideCost == null ? "" : string.Format("{0:N1}", list[j].PesticideCost));
                    datarow.CreateCell(33).SetCellValue(list[j].MulchCost == null ? "" : string.Format("{0:N1}", list[j].MulchCost));
                    datarow.CreateCell(34).SetCellValue(list[j].LaborCost == null ? "" : string.Format("{0:N1}", list[j].LaborCost));
                    datarow.CreateCell(35).SetCellValue(list[j].OtherCost == null ? "" : string.Format("{0:N1}", list[j].OtherCost));
                    datarow.CreateCell(36).SetCellValue(list[j].TotalCost == null ? "" : string.Format("{0:N1}", list[j].TotalCost));
                    datarow.CreateCell(37).SetCellValue(list[j].OAvgMuYield == null ? "" : string.Format("{0:N2}", list[j].OAvgMuYield));
                    datarow.CreateCell(38).SetCellValue(list[j].OAvgPrice == null ? "" : string.Format("{0:N1}", list[j].OAvgPrice));
                    datarow.CreateCell(39).SetCellValue(list[j].OTotalOutput == null ? "" : string.Format("{0:N1}", list[j].OTotalOutput));
                    datarow.CreateCell(40).SetCellValue(list[j].Income == null ? "" : string.Format("{0:N1}", list[j].Income));
                    for (int i = 0; i <= 40; i++)
                    {
                        datarow.GetCell(i).CellStyle = row.GetCell(i).CellStyle;
                    }
                }
            }
            return sheet;
        }


    }
}